package org.fao.pims.persistence;

import java.sql.*;
import java.util.*;
import org.apache.commons.logging.*;
import org.fao.pims.model.*;

public class PollinationNeedDaoImpl extends BaseDao implements PollinationNeedDao {

	private static Log log = LogFactory.getLog(PollinationNeedDaoImpl.class);
	
	/*
	 * Return the pollination need of a crop. The taxon name id must be passed as an argument by
	 * a Taxon object.
	 * (non-Javadoc)
	 * @see org.fao.pims.persistence.PollinationNeedDao#getPollinationNeed(org.fao.pims.model.Taxon)
	 * @return PollinationNeed object
	 */
	public PollinationNeed getPollinationNeed(Taxon crop) throws DataAccessException  {
		
		PreparedStatement pstmt = null;
        ResultSet rset = null;
        Connection conn = null;
        PollinationNeed result = new PollinationNeed();
        
        try {
            
            conn = this.getDatasource().getConnection();          

            String query = 
            "SELECT id, crop_taxon_name, general_impact, specific_impact, fao_commodity," +
            "       breeding_system, poll_without_visitors, source, note FROM (" +
            "SELECT pnd.id, pnd.crop_taxon_name, pnd.general_impact, pnd.specific_impact," +
            "       pnd.fao_commodity, bds.definition AS breeding_system," +
            "       pwv.definition AS poll_without_visitors, pnd.source, pnd.note" +
            "  FROM pims.pollination_need pnd LEFT JOIN" +
            "       pims.pollneeds_concept bds ON bds.code = pnd.breeding_system LEFT JOIN" +
            "       pims.pollneeds_concept pwv ON pwv.code = pnd.poll_without_visitors" +
            " WHERE crop_taxon_name = '" + crop.getId() + "') a";
            
            System.out.println(query);
            
            pstmt = conn.prepareStatement(query);
            
            rset = pstmt.executeQuery();    
            
            List resultList = makeBeanList(resultsetToList(rset));                                                
            
            pstmt.close();
            
            conn.close();   
            
            if( resultList.size() == 0 ) 
            	throw new DataAccessException();
            
            result = (PollinationNeed) resultList.get(0);
            
        } 
        catch(SQLException e) {
            e.printStackTrace();
        }
        finally {
            try 
            { 
                if( ! conn.isClosed() )
                		conn.close();                 
                log.info("Connection closed");                                
            }
            catch(Exception e) {
            	log.error(e.getMessage());
            }           
        }
        return result;
	}
	
	
	protected List makeBeanList(List rows) {
		List beanList = new ArrayList();
        
		System.out.println("rows size: "+ rows.size());
        ListIterator rowIterator = rows.listIterator();
        int rowcounter = 0;
        while(rowIterator.hasNext())
        {
            rowcounter++;
            List fieldlist = (List)rowIterator.next();
            ListIterator fieldsIterator = fieldlist.listIterator();
            Object obj = null;
            String type = "";
            String columnName = "";
            PollinationNeed bean = new PollinationNeed();
            
            while(fieldsIterator.hasNext())
            {
                TableFieldHandler tfhandler = (TableFieldHandler)fieldsIterator.next();
                obj = tfhandler.getObject();
                type = tfhandler.getType();
                columnName = tfhandler.getColumnName().trim();
                // System.out.println(columnName);
                if( obj != null ) {
	                if( "ID".equalsIgnoreCase(columnName) )
	                {
	                    bean.setId( ((Integer)obj).intValue() );                
	                }                	                
	                if( "GENERAL_IMPACT".equalsIgnoreCase(columnName) )
	                {
	                    bean.setGeneralImpact( (String)obj );                    
	                }
	                if( "SPECIFIC_IMPACT".equalsIgnoreCase(columnName) )
	                {
	                    bean.setSpecificImpact( (String)obj );                    
	                }
	                if( "FAO_COMMODITY".equalsIgnoreCase(columnName) )
	                {
	                    bean.setFaoCommodity( (Integer)obj );                    
	                }
	                if( "BREEDING_SYSTEM".equalsIgnoreCase(columnName) )
	                {
	                    bean.setBreedingSystem( (String)obj );                    
	                }                
	                if( "POLL_WITHOUT_VISITORS".equalsIgnoreCase(columnName) )
	                {
	                    bean.setPollinationNoVisitors( (String)obj );                    
	                }	                
	                if( "NOTE".equalsIgnoreCase(columnName) )
	                {
	                    bean.setComment( (String)obj );
	                }
	                if( "SOURCE".equalsIgnoreCase(columnName) )
	                {
	                    bean.setSource( (String)obj );
	                }
	            }
            }
            beanList.add(bean);
         }
        return beanList;
	}
}
